# -*- coding: UTF-8 -*-
# 安装MYSQL DB for python
import MySQLdb as mdb
import redis
import datetime

con = None
redis_user = '10.9.87.202'
# redis_user = '10.13.16.202'
mysql_blued = '10.9.196.184'
# mysql_blued = '10.13.27.242'
# mysql_qa = '10.13.5.157'
mysql_qa = '10.9.128.47'
mysql_chandao = '120.132.59.232'


class PyRedis:
    def __init__(self, host):
        self.host = host
        self.port = 6379
        self.r = redis.StrictRedis(host=self.host, port=self.port)


def yanzhengma(identity):
    r = PyRedis(redis_user)
    test = r.r.keys("*psp:identity*")
    for i in range(len(test)):
        test1 = test[i]
        test2 = r.r.hgetall(test1)
        for (d, x) in test2.items():
            if x == identity:
                for (a, c) in test2.items():
                    if a == 'authcode':
                        return c


def safeEmail(uid, type):
    r = PyRedis(redis_user)
    try:
        if type == 0:
            result = r.r.hgetall("psp:safe_email:%s" % uid)['authcode']
        elif type == 1:
            result = r.r.hgetall("psp:change_safe_email:%s" % uid)['authcode']
            # else:
            #     result = r.r.hgetall("psp:recover:%s"%uid)
    except:
        result = ''
    return result


def check_caifu(uid):
    r = PyRedis(redis_user)
    star_uid = str(uid)
    rich_beans = r.r.hget('u:%s' % star_uid, 'rich_beans')
    rich_level = r.r.hget('u:%s' % star_uid, 'rich_level')
    rich_avatar = r.r.hget('u:%s' % star_uid, 'avatar')
    rich_name = r.r.hget('u:%s' % star_uid, 'name')
    rich_reg_date = r.r.hget('u:%s' % star_uid, 'reg_date')
    return rich_beans, rich_level, rich_avatar, rich_name, rich_reg_date


def chaxu_uid(val1, val2):
    try:
        con = mdb.connect(mysql_blued, 'blued', 'ELS5Pt4Hdx3SfGRU', 'blued', charset='utf8');
        with con:
            cur = con.cursor()
            # 执行一个查询
            state = cur.execute("SELECT uid from users where %s = '%s'" % (val1, val2))
            # 取得上个查询的结果，是单个结果
            result = cur.fetchone()
            if state == 1:
                result_rich = check_caifu(int(result[0]))
                return result, state, result_rich
            else:
                return result, state
    finally:
        con.close()


def change_wandou(parameter1, parameter2, parameter3):
    try:
        con = mdb.connect(mysql_blued, 'blued', 'ELS5Pt4Hdx3SfGRU', 'blued');
        with con:
            cur = con.cursor()
            # 执行修改语句
            rel = cur.execute("select uid from users_ios_sandbox where uid = '%s'" % parameter2)
            if rel == 1 and parameter3 == 'ios':
                parameter3 = 'iossandbox'
            state = cur.execute("update users_sums set beans='%s' where uid='%s' and platform = '%s'" % (
                parameter1, parameter2, parameter3))
            try:
                if state == 0:
                    re = cur.execute(
                        "select beans from users_sums where (uid ='%s' and platform = '%s')" % (parameter2, parameter3))
                    re = str(re)
                    if re == '0':
                        state = cur.execute(
                            "insert INTO users_sums (beans, uid, platform) VALUES ('%s', '%s', '%s')" % (
                                parameter1, parameter2, parameter3))
                    else:
                        bean = str(re[1])[:-2]
                        if re[0] == parameter3 and bean == parameter1:
                            state == 0
            except:
                state == 0
            return state
    finally:
        con.close()


def i_change_wandou(parameter1, parameter2, parameter3):
    try:
        con = mdb.connect(mysql_blued, 'blued', 'ELS5Pt4Hdx3SfGRU', 'blued_oversea');
        with con:
            cur = con.cursor()
            # 执行修改语句
            rel = cur.execute("select uid from users_ios_sandbox where uid = '%s'" % parameter2)
            if rel == 1 and parameter3 == 'ios':
                parameter3 = 'iossandbox'
            state = cur.execute("update users_sums set beans='%s' where uid='%s' and platform = '%s'" % (
                parameter1, parameter2, parameter3))
            try:
                if state == 0:
                    re = cur.execute(
                        "select beans from users_sums where (uid ='%s' and platform = '%s')" % (parameter2, parameter3))
                    re = str(re)
                    if re == '0':
                        state = cur.execute(
                            "insert INTO users_sums (beans, uid, platform) VALUES ('%s', '%s', '%s')" % (
                                parameter1, parameter2, parameter3))
                    else:
                        bean = str(re[1])[:-2]
                        if re[0] == parameter3 and bean == parameter1:
                            state == 0
            except:
                state == 0
            return state
    finally:
        con.close()


def chaxun_yanzhengma():
    try:
        con = mdb.connect(mysql_qa, 'root', 'qatest', 'qa')
        with con:
            cur = con.cursor()
            cur.execute("truncate table qa_identity")
            r = PyRedis(redis_user)
            test = r.r.keys("*psp:identity*")
            for i in range(len(test)):
                test1 = test[i]
                test2 = r.r.hgetall(test1)
                print test2
                pre_auth = ''
                pre_type = ''
                pre_iden = ''
                # if ("verified" in test2.keys() and int(test2["verified"]) == 1) or (
                #                 "pre_verified" not in test2.keys() and int(test2["verified"])) == 1:
                #     continue
                #
                # if ("type" in test2.keys() and test2["type"] != "mobile") and (
                #         "pre_type" in test2.keys() and test2["pre_type"] != "mobile"):
                #     continue
                # if "verified" not in test2.keys() or int(test2["verified"]) == 1:
                #     continue
                # if "type" not in test2.keys() or test2["type"] != "mobile":
                #     continue
                for (d, x) in test2.items():
                    if ("pre_authcode" not in test2.keys()) and ("authcode" not in test2.keys()):
                        continue
                    if d == 'authcode':
                        auth = x
                    elif d == 'type':
                        type = x
                    elif d == 'identity':
                        iden = x
                    elif d == 'pre_authcode':
                        pre_auth = str(x)
                    elif d == 'pre_type':
                        pre_type = str(x)
                    elif d == 'pre_identity':
                        pre_iden = str(x)
                # 执行一个查询
                if pre_auth != '' and pre_type != '' and pre_iden != '':
                    cur.execute("insert INTO qa_identity (authc, acount, ident) VALUES ('%s', '%s', '%s')" % (
                        pre_auth, pre_type, pre_iden))
                try:
                    cur.execute(
                        "insert INTO qa_identity (authc, acount, ident) VALUES ('%s', '%s', '%s')" % (auth, type, iden))
                except:
                    pass
    finally:
        con.close()


def change_star(star_uid, value):
    r = PyRedis(redis_user)
    star_uid = str(star_uid)
    num = str(value)
    star_result = r.r.hset('u:%s' % star_uid, 'vbadge', '%s' % num)
    return star_result


def change_payment(uid, ver):
    r = PyRedis(redis_user)
    uid = str(uid)
    code = '4077bca072b7332f8f68a216441e403d111036e6'
    salt = '88888888'
    if ver == 0:  # 0为国内版
        paycode = 'payment_code'
        paysalt = 'payment_salt'
        db = "blued"
    else:
        db = "blued_oversea"
        paycode = 'payment_code_oversea'
        paysalt = 'payment_salt_oversea'
    try:
        con = mdb.connect(mysql_blued, 'blued', 'ELS5Pt4Hdx3SfGRU', db)
        with con:
            cur = con.cursor()
            # 执行一个查询
            state = cur.execute(
                "update users_payment_code SET code = '%s',salt = '%s' where uid = '%s'" % (code, salt, uid))
            result1 = r.r.hset('u:%s' % uid, paycode, code)
            result2 = r.r.hset('u:%s' % uid, paysalt, salt)
            # 取得上个查询的结果，是单个结果
            result = cur.fetchone()
            return state, result1, result2
    finally:
        con.close()
        # return star_result


def chandao_pie(version, selected):
    try:
        con = mdb.connect(mysql_chandao, 'root', 'qatest', 'zentao')
        with con:
            cur = con.cursor()
            pie = []
            line = []
            localdate = []
            # 执行修改语句
            cur.execute("select DISTINCT name from zt_build WHERE id > 7")
            build = cur.fetchall()
            try:
                if selected != 'trunk':
                    cur.execute("select id from zt_build WHERE product = '%s' and name = '%s'" % (version, selected))
                    reBuild = cur.fetchone()
                else:
                    reBuild = ['trunk']
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and deleted = '0'" % (
                        version, reBuild[0]))
                allpie = cur.fetchall()
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = 'fixed' and deleted = '0'" % (
                        version, reBuild[0]))
                fixed = cur.fetchall()
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = '' and deleted = '0'" % (
                        version, reBuild[0]))
                null = cur.fetchall()
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = 'bydesign' and deleted = '0'" % (
                        version, reBuild[0]))
                bydesign = cur.fetchall()
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = 'notrepro' and deleted = '0'" % (
                        version, reBuild[0]))
                notrepro = cur.fetchall()
                now = datetime.datetime.now()
                for i in range(0, 5):
                    line0 = []
                    date = now + datetime.timedelta(days=-i)
                    localtime = str(date)[0:10]
                    localdate.append(localtime)
                    cur.execute(
                        "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and deleted = '0' and openedDate >= '%s 00:00:00' AND openedDate<= '%s 23:59:59'" % (
                            version, reBuild[0], localtime, localtime))
                    lineput = cur.fetchall()
                    line0.append(lineput)
                    cur.execute(
                        "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution != '' and deleted = '0' and resolvedDate >= '%s 00:00:00' AND resolvedDate<= '%s 23:59:59'" % (
                            version, reBuild[0], localtime, localtime))
                    linefixed = cur.fetchall()
                    line0.append(linefixed)
                    cur.execute(
                        "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = '' and deleted = '0' and openedDate<= '%s 23:59:59'" % (
                            version, reBuild[0], localtime))
                    linebydesign = cur.fetchall()
                    line0.append(linebydesign)
                    cur.execute(
                        "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = 'notrepro' and deleted = '0' and resolvedDate >= '%s 00:00:00' AND resolvedDate<= '%s 23:59:59'" % (
                            version, reBuild[0], localtime, localtime))
                    linenotrepro = cur.fetchall()
                    line0.append(linenotrepro)
                    line.append(line0)
                pie.append(allpie)
                pie.append(fixed)
                pie.append(null)
                pie.append(bydesign)
                pie.append(notrepro)
                return build, pie, line, localdate
            except:
                return 0
    finally:
        con.close()


def delYzm(data):
    r = PyRedis(redis_user)
    res = r.r.delete("psp:daily:%s" % data)
    return res


def blued_show(product):
    try:
        con = mdb.connect(mysql_chandao, 'root', 'qatest', 'zentao', charset='utf8')
        with con:
            cur = con.cursor()
            cur.execute("select name from zt_build WHERE product = %s ORDER BY id DESC LIMIT 0,3" % product)
            build = cur.fetchall()
            cur.execute(
                "select count(*) from zt_bug WHERE product = '%s' and resolution != 'fixed' AND status !='closed' AND deleted = '0'" % (
                    product))
            res_all = int(cur.fetchall()[0][0])
            mybuild = []
            hisData = []
            nowData = []
            bug = []
            req = []
            for bul in build:
                mybug = []
                mbul = str(bul[0])
                cur.execute("select id from zt_build WHERE product = '%s' and name = '%s'" % (product, mbul))
                mbul_id = str(cur.fetchone()[0])
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' AND  deleted = '0'" % (
                        product, mbul_id))
                submit = int(cur.fetchall()[0][0])
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution = '' AND deleted = '0'" % (
                        product, mbul_id))
                les = int(cur.fetchall()[0][0])
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and type = 'designdefect' AND deleted = '0'" % (
                        product, mbul_id))
                designdefect = int(cur.fetchall()[0][0])
                fixed = submit - les
                mybuild.append(mbul)
                mybug.append(submit)
                mybug.append(fixed)
                mybug.append(les)
                bug.append(mybug)
                req.append(designdefect)
                cur.execute(
                    "select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and resolution != 'fixed' AND status !='closed' and deleted = '0'" % (
                        product, mbul_id))
                res_now = int(cur.fetchall()[0][0])
                nowData.append(res_now)
            hisData.append(res_all - nowData[0] - nowData[1] - nowData[2])
            hisData.append(res_all - nowData[0] - nowData[1])
            hisData.append(res_all - nowData[0])
            return mybuild, nowData, hisData, bug, req
            # cur.execute("select count(*) from zt_bug WHERE product = '%s' and openedBuild = '%s' and deleted = '0'")
            # lineput = cur.fetchall()
    finally:
        con.close()
